package database.sporteli;

import database.ConnectionFactory;
import database.util.Queries;
import model.sporteli.ActionInformation;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * Author: Kostika Gorica
 * User: kostika
 * Date: 1/3/13
 * Time: 12:02 PM
 */
public class ActionInformationDao {

    public static int insertActionInformation(ActionInformation action){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(Queries.ACTION_INFORMATION_INSERT_QUERY);
            if (action.getId()<1){
                int nextId = getNextValSequence();
                if(nextId>0){
                    action.setId(nextId);
                }
            }
            statement.setInt(1,action.getId());
            statement.setInt(2, action.getPersonId());
            Date date=action.getActionDate();
            statement.setTimestamp(3, (date!=null)?(new Timestamp(date.getTime())):new Timestamp(new Date().getTime()));
            date = action.getDataInteresimit();
            statement.setTimestamp(4, (date!=null)?(new Timestamp(date.getTime())):new Timestamp(new Date().getTime()));
            statement.setInt(5,action.getFushaInteresimitID());
            if(action.getNenFushaInteresimitID()>0){
                statement.setInt(6,action.getNenFushaInteresimitID());
            } else {
                statement.setNull(6,Types.INTEGER);
            }
            if(action.getInformationSourceID()>0){
                statement.setInt(7,action.getInformationSourceID());
            } else {
                statement.setNull(7,Types.INTEGER);
            }
            if(action.getInformationSubSourceID()>0){
                statement.setInt(8,action.getInformationSubSourceID());
            } else {
                statement.setNull(8,Types.INTEGER);
            }
            statement.setInt(9,action.getNoDocuments());
            statement.setString(10,action.getNotes());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            System.err.println("ActionInformationDao.insertActionInformation Error db");
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                System.err.println("ActionInformationDao.getActivities finally block error");
                e.printStackTrace();
            }
        }
        return rows;
    }



    public static ArrayList<ActionInformation> selectActionInformationBetwen(Date start, Date end){
        ArrayList<ActionInformation> result = new ArrayList<ActionInformation>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            if (start==null || end==null){
                throw new Exception("NULL parameters not Allowed") ;
            }
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(Queries.ACTION_INFORMATION_SELECT_QUERY);
            statement.setTimestamp(1,new Timestamp(start.getTime()));
            statement.setTimestamp(2,new Timestamp(end.getTime()));
            resultSet = statement.executeQuery();
            result = toActionInformationList(resultSet);
        } catch (SQLException e) {
            System.err.println("ActionInformationDao.getActivities Error db");
            e.printStackTrace();
        } catch (Exception e) {
            System.err.println("ActivityDao.selectActionInformationBetwen Error db");
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                System.err.println("ActionInformationDao.selectActionInformationBetwen finally block error");
                e.printStackTrace();
            }
        }
        return result;
    }



    public static int getNextValSequence(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int nextId = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(Queries.SELECT_NEXTVAL_SEQUENCE_QUERY);
            statement.setString(1,"sporteli.action_information_sequence");
            resultSet = statement.executeQuery();
            if (resultSet.next()){
               nextId = resultSet.getInt(1);
            }

        } catch (SQLException e) {
            System.err.println("ActionInformationDao.getNextValSequence Error db");
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (resultSet!=null) resultSet.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                System.err.println("ActionInformationDao.getNextValSequence finally block error");
            }
        }
        return nextId;
    }





    private static ArrayList<ActionInformation> toActionInformationList(ResultSet rs){
        ArrayList<ActionInformation> result = new ArrayList<ActionInformation>();
        try {
            while (rs.next()) {
                ActionInformation action = toActionInformation(rs);
                result.add(action);
            }
        } catch (SQLException e) {
            System.err.println("toActionInformationList ResultSetError");
            e.printStackTrace();
        } catch (Exception e) {
            System.err.println("toActionInformation unknown ResultSetError");
            e.printStackTrace();
        }
        return result;
    }

    private static ActionInformation toActionInformation(ResultSet rs) throws SQLException {
        if (rs == null || rs.isClosed()){
            throw new SQLException("ResultSet Closed !!!");
        }
        ActionInformation action;
        action=new ActionInformation();
        action.setId(rs.getInt(1));
        action.setPersonId(rs.getInt(2));
        action.setActionDate(rs.getTimestamp(3));
        action.setDataInteresimit(rs.getTimestamp(4));
        action.setFushaInteresimitID(rs.getInt(5));
        action.setNenFushaInteresimitID(rs.getInt(6));
        action.setInformationSourceID(rs.getInt(7));
        action.setInformationSubSourceID(rs.getInt(8));
        action.setNoDocuments(rs.getInt(9));
        action.setNotes(rs.getString(10));

        return action;
    }
}
